using System;
using System.Data;
using System.Collections;
using APP.QUANLY.Common;
using RMAS.DAL;

namespace RMAS.DAL
{
    /// <summary>
    /// Summary description for DM_WAREHOUSEMETERIAL.
    /// </summary>
    public class DM_WAREHOUSEMETERIALDao
    {
        private string _SQLSelectByKey;
        private string _SQLUpdate;
        private string _SQLInsert;
        private string _SQLDelete;
        private string _SQLSelectAll;

        #region Properties

        public IDbConnection _dbConnection;

        public IDbConnection DbConnection
        {
            get { return _dbConnection; }
            set { _dbConnection = value; }
        }

        #endregion

        #region Constructor

        public DM_WAREHOUSEMETERIALDao()
        {
            _dbConnection = null;
            InitSqlQuery();
        }

        public DM_WAREHOUSEMETERIALDao(IDbConnection con)
        {
            _dbConnection = con;
            InitSqlQuery();
        }

        #endregion


        private void ToLowerQuery()
        {
            _SQLDelete = _SQLDelete.ToLower();
            _SQLUpdate = _SQLUpdate.ToLower();
            _SQLInsert = _SQLInsert.ToLower();
            _SQLSelectByKey = _SQLSelectByKey.ToLower();
        }

        private void ToUpperQuery()
        {
            _SQLDelete = _SQLDelete.ToUpper();
            _SQLUpdate = _SQLUpdate.ToUpper();
            _SQLInsert = _SQLInsert.ToUpper();
            _SQLSelectByKey = _SQLSelectByKey.ToUpper();
        }

        #region InitSqlQuery
        private void InitSqlQuery()
        {
            string query;

            query = @"SELECT WHID, MID, MName, FirstBalance, LastBalance, addUp, addDown, Month, Year, Log, IDPOS FROM DM_WAREHOUSEMETERIAL WHERE (WHID = @WHID AND 
MID = @MID)";
            _SQLSelectByKey = query;

            query = @"UPDATE DM_WAREHOUSEMETERIAL SET MName = @MName, FirstBalance = @FirstBalance, LastBalance = @LastBalance, addUp = @addUp, addDown = @addDown, Month = @Month, Year = @Year, Log = @Log, IDPOS = @IDPOS WHERE (WHID = @WHID AND 
MID = @MID)";
            _SQLUpdate = query;

            query = @"INSERT INTO DM_WAREHOUSEMETERIAL VALUES (@WHID, @MID, @MName, @FirstBalance, @LastBalance, @addUp, @addDown, @Month, @Year, @Log, @IDPOS)";
            _SQLInsert = query;

            query = @"DELETE FROM DM_WAREHOUSEMETERIAL WHERE (WHID = @WHID AND 
MID = @MID)";
            _SQLDelete = query;

            query = @"SELECT WHID, MID, MName, FirstBalance, LastBalance, addUp, addDown, Month, Year, Log, IDPOS FROM DM_WAREHOUSEMETERIAL";
            _SQLSelectAll = query;
        }
        #endregion
        public void LoadByCommandText(string commandtext, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = commandtext;

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        #region Save & Delete
        private void Save(DM_WAREHOUSEMETERIAL obj, IDbTransaction trans, bool isExisting)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command = _dbConnection.CreateCommand();

                if (!isExisting) { command.CommandText = _SQLInsert + ";"; }
                else { command.CommandText = _SQLUpdate + ";"; }

                DalTools.addDbDataParameter(command, "WHID", obj.WHID, DbType.String);
                DalTools.addDbDataParameter(command, "MID", obj.MID, DbType.String);
                DalTools.addDbDataParameter(command, "MName", obj.MName, DbType.String);
                DalTools.addDbDataParameter(command, "FirstBalance", obj.FirstBalance, DbType.Int32);
                DalTools.addDbDataParameter(command, "LastBalance", obj.LastBalance, DbType.Int32);
                DalTools.addDbDataParameter(command, "addUp", obj.addUp, DbType.Int32);
                DalTools.addDbDataParameter(command, "addDown", obj.addDown, DbType.Int32);
                DalTools.addDbDataParameter(command, "Month", obj.Month, DbType.Int16);
                DalTools.addDbDataParameter(command, "Year", obj.Year, DbType.Int32);
                DalTools.addDbDataParameter(command, "Log", obj.Log, DbType.String);
                DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.String);

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Delete(DM_WAREHOUSEMETERIAL obj, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                //throw new DalException("Connection has not been set.");
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = _SQLDelete + ";";

                DalTools.addDbDataParameter(command, "WHID", obj.WHID, DbType.String);
                DalTools.addDbDataParameter(command, "MID", obj.MID, DbType.String);

                if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Insert(DM_WAREHOUSEMETERIAL obj, IDbTransaction trans)
        {
            Save(obj, trans, false);
        }
        public void Update(DM_WAREHOUSEMETERIAL obj, IDbTransaction trans)
        {
            Save(obj, trans, true);
        }
        #endregion

        #region Command Execute & Read Properties

        private DM_WAREHOUSEMETERIALList SelectListByCommand(IDbCommand command)
        {
            DM_WAREHOUSEMETERIALList list = new DM_WAREHOUSEMETERIALList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                DM_WAREHOUSEMETERIAL obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        obj = new DM_WAREHOUSEMETERIAL();
                        ReadProperties(obj, reader);
                        list.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private DM_WAREHOUSEMETERIALList SelectListByCommand(IDbCommand command, IDbTransaction trans)
        {
            DM_WAREHOUSEMETERIALList list = new DM_WAREHOUSEMETERIALList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                DM_WAREHOUSEMETERIAL obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command.Transaction = trans;
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        obj = new DM_WAREHOUSEMETERIAL();
                        ReadProperties(obj, reader);
                        list.Add(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private void DeleteListByCommand(IDbCommand command, IDbTransaction trans)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                if (trans != null) { command.Transaction = trans; }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void ReadProperties(DM_WAREHOUSEMETERIAL obj, IDataReader reader)
        {
            try
            {
                if (!(reader["WHID"] is DBNull))
                {
                    obj.WHID = (string)reader["WHID"];
                }
                if (!(reader["MID"] is DBNull))
                {
                    obj.MID = (string)reader["MID"];
                }
                if (!(reader["MName"] is DBNull))
                {
                    obj.MName = (string)reader["MName"];
                }
                if (!(reader["FirstBalance"] is DBNull))
                {
                    obj.FirstBalance = (Int32)reader["FirstBalance"];
                }
                if (!(reader["LastBalance"] is DBNull))
                {
                    obj.LastBalance = (Int32)reader["LastBalance"];
                }
                if (!(reader["addUp"] is DBNull))
                {
                    obj.addUp = (Int32)reader["addUp"];
                }
                if (!(reader["addDown"] is DBNull))
                {
                    obj.addDown = (Int32)reader["addDown"];
                }
                if (!(reader["Month"] is DBNull))
                {
                    obj.Month = (Int16)reader["Month"];
                }
                if (!(reader["Year"] is DBNull))
                {
                    obj.Year = (Int32)reader["Year"];
                }
                if (!(reader["Log"] is DBNull))
                {
                    obj.Log = (string)reader["Log"];
                }
                if (!(reader["IDPOS"] is DBNull))
                {
                    obj.IDPOS = (string)reader["IDPOS"];
                }
            }
            catch (Exception ex)
            {
                throw new DalException("Failed to read properties from DataReader.", ex);
            }
        }
        #endregion

        public DM_WAREHOUSEMETERIAL SelectByKey(string WHID, string MID)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
            DalTools.addDbDataParameter(command, "WHID", WHID, DbType.String);
            DalTools.addDbDataParameter(command, "MID", MID, DbType.String);
            DM_WAREHOUSEMETERIALList list = SelectListByCommand(command);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public DM_WAREHOUSEMETERIAL SelectByKey(string WHID, string MID, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
            DalTools.addDbDataParameter(command, "WHID", WHID, DbType.String);
            DalTools.addDbDataParameter(command, "MID", MID, DbType.String);
            DM_WAREHOUSEMETERIALList list = SelectListByCommand(command, trans);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public DM_WAREHOUSEMETERIALList SelectAll()
        {
            IDbCommand command = _dbConnection.CreateCommand();
            if (!StaticVar.IsServer)
            {
                _SQLSelectAll = _SQLSelectAll + " WHERE IDPOS = @IDPOS";
                DalTools.addDbDataParameter(command, "IDPOS", StaticVar.CPUID, DbType.String);
            }
            command.CommandText = _SQLSelectAll;
            DM_WAREHOUSEMETERIALList list = SelectListByCommand(command);
            return list;
        }
        public DM_WAREHOUSEMETERIALList SelectAll(IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            if (!StaticVar.IsServer)
            {
                _SQLSelectAll = _SQLSelectAll + " WHERE IDPOS = @IDPOS";
                DalTools.addDbDataParameter(command, "IDPOS", StaticVar.CPUID, DbType.String);
            }
            command.CommandText = _SQLSelectAll;
            DM_WAREHOUSEMETERIALList list = SelectListByCommand(command, trans);
            return list;
        }
        public DM_WAREHOUSEMETERIALList SelectByCommand(string Command)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = Command;
            DM_WAREHOUSEMETERIALList list = SelectListByCommand(command);
            return list;
        }

        public DM_WAREHOUSEMETERIALList SelectByCondition(DM_WAREHOUSEMETERIAL obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM DM_WAREHOUSEMETERIAL " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DM_WAREHOUSEMETERIALList list = SelectListByCommand(command, trans);
            return list;
        }

        public DM_WAREHOUSEMETERIALList SelectByCondition(DM_WAREHOUSEMETERIAL obj)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM DM_WAREHOUSEMETERIAL " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DM_WAREHOUSEMETERIALList list = SelectListByCommand(command);
            return list;
        }
        private void DeleteByCondition(DM_WAREHOUSEMETERIAL obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "DELETE FROM DM_WAREHOUSEMETERIAL " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DeleteListByCommand(command, trans);
        }

        #region Make Condition Query & Fill Param from Condition to Command
        private string GetCondtionQuery(DM_WAREHOUSEMETERIAL obj)
        {
            if (obj == null) { return ""; }
            string s = "";
            if (obj.IsSetWHID)
            {
                s += " AND WHID = @WHID ";
            }
            if (obj.IsSetMID)
            {
                s += " AND MID = @MID ";
            }
            if (obj.IsSetMName)
            {
                s += " AND MName = @MName ";
            }
            if (obj.IsSetFirstBalance)
            {
                s += " AND FirstBalance = @FirstBalance ";
            }
            if (obj.IsSetLastBalance)
            {
                s += " AND LastBalance = @LastBalance ";
            }
            if (obj.IsSetAddUp)
            {
                s += " AND addUp = @addUp ";
            }
            if (obj.IsSetAddDown)
            {
                s += " AND addDown = @addDown ";
            }
            if (obj.IsSetMonth)
            {
                s += " AND Month = @Month ";
            }
            if (obj.IsSetYear)
            {
                s += " AND Year = @Year ";
            }
            if (obj.IsSetLog)
            {
                s += " AND Log = @Log ";
            }
            if (obj.IsSetIDPOS)
            {
                s += " AND IDPOS = @IDPOS ";
            }
            if (s.Length > 0)
            {
                s = " WHERE " + s.Substring(4);
                return s;
            }
            return "";
        }

        private void FillParamToCommand(IDbCommand command, DM_WAREHOUSEMETERIAL obj)
        {
            if (obj == null) { return; }
            if (obj.IsSetWHID)
            {
                DalTools.addDbDataParameter(command, "WHID", obj.WHID, DbType.String);
            }
            if (obj.IsSetMID)
            {
                DalTools.addDbDataParameter(command, "MID", obj.MID, DbType.String);
            }
            if (obj.IsSetMName)
            {
                DalTools.addDbDataParameter(command, "MName", obj.MName, DbType.String);
            }
            if (obj.IsSetFirstBalance)
            {
                DalTools.addDbDataParameter(command, "FirstBalance", obj.FirstBalance, DbType.Int32);
            }
            if (obj.IsSetLastBalance)
            {
                DalTools.addDbDataParameter(command, "LastBalance", obj.LastBalance, DbType.Int32);
            }
            if (obj.IsSetAddUp)
            {
                DalTools.addDbDataParameter(command, "addUp", obj.addUp, DbType.Int32);
            }
            if (obj.IsSetAddDown)
            {
                DalTools.addDbDataParameter(command, "addDown", obj.addDown, DbType.Int32);
            }
            if (obj.IsSetMonth)
            {
                DalTools.addDbDataParameter(command, "Month", obj.Month, DbType.Int16);
            }
            if (obj.IsSetYear)
            {
                DalTools.addDbDataParameter(command, "Year", obj.Year, DbType.Int32);
            }
            if (obj.IsSetLog)
            {
                DalTools.addDbDataParameter(command, "Log", obj.Log, DbType.String);
            }
            if (obj.IsSetIDPOS)
            {
                DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.String);
            }
        }
        #endregion
    }
}